package com.realtyManage.springboot.Dao;


import com.realtyManage.springboot.Model.Do.HouseDetail;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

@Repository
public interface HouseDetailRepository  extends JpaRepository<HouseDetail, Integer>, JpaSpecificationExecutor<HouseDetail> {
    @Override
    HouseDetail getOne(Integer id);

    @Modifying
    @Transactional
    @Query("update HouseDetail a set " +
            "a.indoorArea = CASE WHEN :#{#houseDetail.indoorArea} IS NULL THEN a.indoorArea ELSE :#{#houseDetail.indoorArea} END ," +
            "a.blockCode = CASE WHEN :#{#houseDetail.blockCode} IS NULL THEN a.blockCode ELSE :#{#houseDetail.blockCode} END ," +
            "a.unitCode = CASE WHEN :#{#houseDetail.unitCode} IS NULL THEN a.unitCode ELSE :#{#houseDetail.unitCode} END ," +
            "a.doorplateCode = CASE WHEN :#{#houseDetail.doorplateCode} IS NULL THEN a.doorplateCode ELSE :#{#houseDetail.doorplateCode} END ," +
            "a.architectureArea = CASE WHEN :#{#houseDetail.architectureArea} IS NULL THEN a.architectureArea ELSE :#{#houseDetail.architectureArea} END ," +
            "a.face = CASE WHEN :#{#houseDetail.face} IS NULL THEN a.face ELSE :#{#houseDetail.face} END ," +
            "a.oldPrice = CASE WHEN :#{#houseDetail.oldPrice} IS NULL THEN a.oldPrice ELSE :#{#houseDetail.oldPrice} END ," +
            "a.oldTotal = CASE WHEN :#{#houseDetail.oldTotal} IS NULL THEN a.oldTotal ELSE :#{#houseDetail.oldTotal} END ," +
            "a.discountPrice = CASE WHEN :#{#houseDetail.discountPrice} IS NULL THEN a.discountPrice ELSE :#{#houseDetail.discountPrice} END ," +
            "a.discountTotal = CASE WHEN :#{#houseDetail.discountTotal} IS NULL THEN a.discountTotal ELSE :#{#houseDetail.discountTotal} END ," +
            "a.type = CASE WHEN :#{#houseDetail.type} IS NULL THEN a.type ELSE :#{#houseDetail.type} END ," +
            "a.special = CASE WHEN :#{#houseDetail.special} IS NULL THEN a.special ELSE :#{#houseDetail.special} END ," +
            "a.addUid = CASE WHEN :#{#houseDetail.addUid} IS NULL THEN a.addUid ELSE :#{#houseDetail.addUid} END ," +
            "a.artificial = CASE WHEN :#{#houseDetail.artificial} IS NULL THEN a.artificial ELSE :#{#houseDetail.artificial} END ," +
            "a.stage = CASE WHEN :#{#houseDetail.stage} IS NULL THEN a.stage ELSE :#{#houseDetail.stage} END ," +
            "a.relatedParty = CASE WHEN :#{#houseDetail.relatedParty} IS NULL THEN a.relatedParty ELSE :#{#houseDetail.relatedParty} END ," +
            "a.relatedAdviser = CASE WHEN :#{#houseDetail.relatedAdviser} IS NULL THEN a.relatedAdviser ELSE :#{#houseDetail.relatedAdviser} END ," +
            "a.cjTime = CASE WHEN :#{#houseDetail.cjTime} IS NULL THEN a.cjTime ELSE :#{#houseDetail.cjTime} END ," +
            "a.totalActualAmount = CASE WHEN :#{#houseDetail.totalActualAmount} IS NULL THEN a.totalActualAmount ELSE :#{#houseDetail.totalActualAmount} END ," +
            "a.artificial = CASE WHEN :#{#houseDetail.artificial} IS NULL THEN a.artificial ELSE :#{#houseDetail.artificial} END ," +
            "a.addUserName = CASE WHEN :#{#houseDetail.addUserName} IS NULL THEN a.addUserName ELSE :#{#houseDetail.addUserName} END " +
            "where a.id = :#{#houseDetail.id}")
    int update(@Param("houseDetail") HouseDetail houseDetail);

    @Query("select id from HouseDetail where name like %?1%")
    List<String> findByNameLike(String houseName);

    @Query(value = "select name from house_detail where id = ?1",nativeQuery = true)
    String getName(Integer id);

    @Query(value = "select manage_house_id from user where id = ?1",nativeQuery = true)
    String getId(String id);

    @Modifying
    @Transactional
    @Query(value = "delete from house_detail where id in(?1) ",nativeQuery = true)
    int deleteHouse(List id);

    @Query(value = "select * from house_detail where special = 1 ",nativeQuery = true)
    List<HouseDetail> querySpecial();

    @Query(value = "select DISTINCT architecture_area from house_detail ORDER BY architecture_area",nativeQuery = true)
    List<String> getArchitectureArea();

    @Query(value = "select DISTINCT property_tye from house_detail",nativeQuery = true)
    List<String> getPropertyTye();

    @Query(value = "select DISTINCT block_code from house_detail ORDER BY block_code",nativeQuery = true)
    List<String> getBlockCode();

    @Query(value = "select DISTINCT floor from house_detail ORDER BY floor",nativeQuery = true)
    List<String> getFloor();

    @Query(value = "select DISTINCT apartment_number from house_detail ORDER BY apartment_number",nativeQuery = true)
    List<String> getApartmentNumber();

    @Query(value = "select DISTINCT apartment_structure from house_detail ORDER BY LEFT(apartment_structure,1)",nativeQuery = true)
    List<String> getApartmentStructure();

    @Query(value = "select DISTINCT jacketed from house_detail ORDER BY jacketed",nativeQuery = true)
    List<String> getJacketed();

    @Query(value = "select DISTINCT discount_price from house_detail ORDER BY discount_price",nativeQuery = true)
    List<String> getDiscountPrice();

    @Query(value = "select DISTINCT discount_total from house_detail ORDER BY discount_total",nativeQuery = true)
    List<String> getDiscountTotal();

    @Query(value = "SELECT COUNT(1) ks FROM house_detail WHERE block_code = ?1 AND type = '0'",nativeQuery = true)
   int countSalable(String blockCode);

    @Query(value = "SELECT COUNT(1) ks FROM house_detail WHERE block_code = ?1 AND type IN (1,2)",nativeQuery = true)
    int countFixed(String blockCode);

    @Query(value = "SELECT COUNT(1) ks FROM house_detail WHERE block_code = ?1 AND type IN (3,4)",nativeQuery = true)
    int countSold(String blockCode);

    @Query(value = "select DISTINCT stage from house_detail",nativeQuery = true)
    List<String> getStage();

    @Query(value = "SELECT * FROM house_detail WHERE type NOT IN (3,4)",nativeQuery = true)
    List<HouseDetail> showOnSaleHouseList();

    @Query(value = "SELECT level FROM customer_registration WHERE level  IN (?1)",nativeQuery = true)
    List getTest(List type);

    @Query(value = "SELECT block_code,unit_code,floor,doorplate_code,type,sale_type FROM `house_detail` ORDER BY block_code,unit_code ,floor,CONVERT(doorplate_code  USING gbk)",nativeQuery = true)
    List getXk();

    @Query(value = "SELECT apartment_number,COUNT(1) num FROM house_detail GROUP BY apartment_number",nativeQuery = true)
    List getApartment();

    @Query(value = "SELECT apartment_number,COUNT(1) num FROM house_detail GROUP BY apartment_number",nativeQuery = true)
    List getApartmentNumberList(String code);

    @Query(value = "SELECT id FROM house_detail where apartment_number = ?1",nativeQuery = true)
    List getLen(String code);

    @Query(value = "SELECT * FROM(\n" +
            "(SELECT apartment_structure  FROM house_detail WHERE apartment_number = ?1 LIMIT 1) a,\n" +
            "(SELECT FORMAT(SUM(architecture_area)/?2,0) avgArea FROM house_detail WHERE apartment_number = ?1) b,\n" +
            "(SELECT FORMAT(SUM(discount_total)/?2,0) avgJg FROM house_detail WHERE apartment_number = ?1) c, \n" +
            "(SELECT COUNT(DISTINCT type) type FROM house_detail WHERE apartment_number = ?1 AND type = '0' LIMIT 1) d" +
            ")",nativeQuery = true)
    List getList(String code,Integer len);

    @Query(value = "SELECT * FROM house_detail where id = ?1",nativeQuery = true)
    List<HouseDetail> getHouseDetailById(String id);


    @Modifying
    @Transactional
    @Query("update HouseDetail a set " +
            "a.indoorArea = CASE WHEN :#{#houseDetail.indoorArea} IS NULL THEN a.indoorArea ELSE :#{#houseDetail.indoorArea} END ," +
            "a.blockCode = CASE WHEN :#{#houseDetail.blockCode} IS NULL THEN a.blockCode ELSE :#{#houseDetail.blockCode} END ," +
            "a.unitCode = CASE WHEN :#{#houseDetail.unitCode} IS NULL THEN a.unitCode ELSE :#{#houseDetail.unitCode} END ," +
            "a.doorplateCode = CASE WHEN :#{#houseDetail.doorplateCode} IS NULL THEN a.doorplateCode ELSE :#{#houseDetail.doorplateCode} END ," +
            "a.architectureArea = CASE WHEN :#{#houseDetail.architectureArea} IS NULL THEN a.architectureArea ELSE :#{#houseDetail.architectureArea} END ," +
            "a.face = CASE WHEN :#{#houseDetail.face} IS NULL THEN a.face ELSE :#{#houseDetail.face} END ," +
            "a.oldPrice = CASE WHEN :#{#houseDetail.oldPrice} IS NULL THEN a.oldPrice ELSE :#{#houseDetail.oldPrice} END ," +
            "a.oldTotal = CASE WHEN :#{#houseDetail.oldTotal} IS NULL THEN a.oldTotal ELSE :#{#houseDetail.oldTotal} END ," +
            "a.discountPrice = CASE WHEN :#{#houseDetail.discountPrice} IS NULL THEN a.discountPrice ELSE :#{#houseDetail.discountPrice} END ," +
            "a.discountTotal = CASE WHEN :#{#houseDetail.discountTotal} IS NULL THEN a.discountTotal ELSE :#{#houseDetail.discountTotal} END ," +
            "a.type = CASE WHEN :#{#houseDetail.type} IS NULL THEN a.type ELSE :#{#houseDetail.type} END ," +
            "a.special = CASE WHEN :#{#houseDetail.special} IS NULL THEN a.special ELSE :#{#houseDetail.special} END ," +
            "a.addUid = CASE WHEN :#{#houseDetail.addUid} IS NULL THEN a.addUid ELSE :#{#houseDetail.addUid} END ," +
            "a.artificial = CASE WHEN :#{#houseDetail.artificial} IS NULL THEN a.artificial ELSE :#{#houseDetail.artificial} END ," +
            "a.stage = CASE WHEN :#{#houseDetail.stage} IS NULL THEN a.stage ELSE :#{#houseDetail.stage} END ," +
            "a.relatedParty = CASE WHEN :#{#houseDetail.relatedParty} IS NULL THEN a.relatedParty ELSE :#{#houseDetail.relatedParty} END ," +
            "a.relatedAdviser = CASE WHEN :#{#houseDetail.relatedAdviser} IS NULL THEN a.relatedAdviser ELSE :#{#houseDetail.relatedAdviser} END ," +
            "a.cjTime = CASE WHEN :#{#houseDetail.cjTime} IS NULL THEN a.cjTime ELSE :#{#houseDetail.cjTime} END ," +
            "a.totalActualAmount = CASE WHEN :#{#houseDetail.totalActualAmount} IS NULL THEN a.totalActualAmount ELSE :#{#houseDetail.totalActualAmount} END ," +
            "a.addUserName = CASE WHEN :#{#houseDetail.addUserName} IS NULL THEN a.addUserName ELSE :#{#houseDetail.addUserName} END " +
            "where a.relatedParty = :#{#houseDetail.relatedParty}")
    int updateHouseType(@Param("houseDetail") HouseDetail houseDetail);

    @Modifying
    @Transactional
    @Query(value = "update house_detail set cj_time = '',total_actual_amount = '',related_party = '',related_adviser = '',type = 0,artificial = ?2 " +
            " where related_party = ?1",nativeQuery = true)
    int updateHouseTypeN(Integer id,Integer artificial);

    @Modifying
    @Transactional
    @Query(value = "update house_detail set cj_time = '',total_actual_amount = '',related_party = '',related_adviser = '',type = 0 " +
            " where related_party = ?1",nativeQuery = true)
    int updateHouseTypeN(Integer id);


    @Query(value = "SELECT * FROM house_detail where block_code = ?1 and  doorplate_code = ?2",nativeQuery = true)
    HouseDetail queryHouseDetail(Integer blockCode,String doorplateCode);

    @Query(value = "SELECT * FROM house_detail where block_code = ?1 and  doorplate_code = ?2 and unit_code = ?3",nativeQuery = true)
    HouseDetail queryHouseDetailZ(Integer blockCode,String doorplateCode,String unitCode);

    /*@Modifying
    @Transactional
    @Query(value = "update house_detail set cj_time = '',total_actual_amount = '',related_party = '',related_adviser = '' ,type = 0 " +
            "where relatedParty = ?1",nativeQuery = true)
    int transferUser(String inputUser, String outUser);*/

    @Query(value = "SELECT DISTINCT(block_code) FROM house_detail WHERE 1 = 1 ORDER BY block_code",nativeQuery = true)
    List<String> queryBlockCode();

    @Query(value = "SELECT DISTINCT(property_tye) FROM house_detail WHERE 1 = 1 ORDER BY property_tye",nativeQuery = true)
    List<String> queryPropertyType();

    @Query(value = "SELECT DISTINCT(doorplate_code) FROM house_detail WHERE 1 = 1 AND block_code = ?1 and unit_code = ?2 ORDER BY doorplate_code",nativeQuery = true)
    List<String> queryDoorplateCode(String blockCode,String unitCode);

    @Query(value = "SELECT DISTINCT(block_code) FROM house_detail WHERE 1 = 1 AND property_tye = ?1 and stage = ?2 ORDER BY block_code",nativeQuery = true)
    List<String> queryTung(String blockCode,String unitCode);

    @Query(value = "SELECT DISTINCT(block_code) FROM house_detail WHERE 1 = 1 AND property_tye = ?1 ORDER BY block_code",nativeQuery = true)
    List<String> queryTungZ(String propertyType);

    @Query(value = "SELECT DISTINCT(unit_code) FROM house_detail WHERE 1 = 1 AND block_code = ?1 ORDER BY unit_code",nativeQuery = true)
    List<String> queryUnitCode(String propertyType);

    @Query(value = "SELECT DISTINCT(stage) FROM house_detail WHERE 1 = 1 AND property_tye = ?1 ORDER BY stage",nativeQuery = true)
    List<String> queryStage(String propertyType);

    @Query(value = "SELECT count(1) FROM house_detail WHERE 1 = 1 AND related_party = ?1",nativeQuery = true)
    int countNum(Integer relatedParty);

    @Query(value = "SELECT DISTINCT(doorplate_code) FROM house_detail WHERE type = 0 AND block_code = ?1",nativeQuery = true)
    List<String> queryDoorplateCodeZ(String blockCode);

    @Query(value = "SELECT id FROM house_detail WHERE block_code = ?1 AND unit_code = ?2 AND doorplate_code = ?3",nativeQuery = true)
    String queryZsIdS(Integer blockCode,String unitCode,String doorplateCode);

    @Query(value = "SELECT id FROM house_detail WHERE block_code = ?1 AND doorplate_code = ?2 LIMIT 1",nativeQuery = true)
    String queryZsIdE(Integer blockCode,String doorplateCode);

    @Query(value = "SELECT * FROM house_detail WHERE block_code = ?1 AND unit_code = ?2 AND doorplate_code = ?3",nativeQuery = true)
    HouseDetail getBlockCodeY(Integer blockCode,String unitCode,String doorplateCode);

    @Query(value = "SELECT * FROM house_detail WHERE block_code = ?1 AND doorplate_code = ?2",nativeQuery = true)
    HouseDetail getBlockCodeC(Integer blockCode,String doorplateCode);

    @Query(value = "SELECT * FROM house_detail WHERE  related_party = ?1",nativeQuery = true)
    HouseDetail countHouse(Integer relatedParty);

    @Query(value = "select DISTINCT floor from house_detail WHERE block_code = ?1 ORDER BY floor",nativeQuery = true)
    List<String> getFloorE(String blockCode);
}
